﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Frame.DataHelper;
using System.Data.SqlClient;
using System.Data;

namespace Frame.Global
{
    public abstract class GlobalSqlHelper
    {
        #region 事务操作
        /// <summary>
        /// 返回SQL连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection NewConnection()
        {
            SqlConnection sqlconn = new SqlConnection(GlobalRunParms.PubdataConncetionString);
            return sqlconn;
        }
        /// <summary>
        /// 新建一个新的事务
        /// </summary>
        /// <param name="conn">事务基础的连接对象</param>
        /// <returns>返回事务类</returns>
        public static SqlTransaction BeginTransaction(SqlConnection conn)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            return conn.BeginTransaction();
        }
        /// <summary>
        /// 新建一个新的事务
        /// </summary>
        /// <returns>返回事务类</returns>
        public static SqlTransaction BeginTransaction()
        {
            SqlConnection sqlconn = new SqlConnection(GlobalRunParms.PubdataConncetionString);
            sqlconn.Open();
            return sqlconn.BeginTransaction();
        }
        /// <summary>
        /// 结束事务
        /// </summary>
        /// <param name="sqltrans">需要结束的事务。</param>
        //public static void EndTransaction(SqlTransaction sqltrans)
        //{
        //    try
        //    {
        //        SqlConnection sqlconn = sqltrans.Connection;
        //        sqlconn.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        /// <summary>
        /// 对事物执行Commit
        /// </summary>
        /// <param name="sqltrans">需要执行Commit的事务。</param>
        public static void Commit(SqlTransaction sqltrans)
        {
            sqltrans.Commit();
        }
        /// <summary>
        /// 对事物执行Rollback
        /// </summary>
        /// <param name="sqltrans">执行回滚的事务</param>
        public static void Rollback(SqlTransaction sqltrans)
        {
            sqltrans.Rollback();
        }
        /// <summary>
        /// 对事物执行Rollback到某保存点
        /// </summary>
        /// <param name="sqltrans">执行回滚的事务</param>
        /// <param name="Pointname">回滚的保存点名称</param>
        public static void Rollback(SqlTransaction sqltrans, string Pointname)
        {
            sqltrans.Rollback(Pointname);
        }
        /// <summary>
        /// 给现有事务设置用于回滚的保存点
        /// </summary>
        /// <param name="sqltrans">事务</param>
        /// <param name="Pointname">保存点名称</param>
        public static void SavePoint(SqlTransaction sqltrans, string Pointname)
        {
            sqltrans.Save(Pointname);
        }
        #endregion

        #region SQL执行方法
        /// <summary>
        /// 直接执行Sql语句，返回布尔值表示成功与否
        /// </summary>
        /// <param name="sqlstr">Sql语句</param>
        /// <returns></returns>
        public static bool ExecuteSQL(string sqlstr)
        {
            try
            {
                int i = CssSQLHelper.CssExecuteNonQuery(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 使用事务直接执行Sql语句，返回布尔值表示成功与否
        /// </summary>
        /// <param name="sqlstr">Sql语句</param>
        /// <param name="sqltrans">事务</param>
        /// <returns></returns>
        public static bool ExecuteSQL(string sqlstr, SqlTransaction sqltrans)
        {
            try
            {
                int i = CssSQLHelper.CssExecuteNonQuery(sqltrans, CommandType.Text, sqlstr, null);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行存储过程，返回布尔值表示成功与否
        /// </summary>
        /// <param name="Procname">存储过程名称</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static bool ExecuteProc(string Procname, params SqlParameter[] parms)
        {
            try
            {
                int i = CssSQLHelper.CssExecuteNonQuery(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 使用事务执行存储过程，返回布尔值表示成功与否
        /// </summary>
        /// <param name="Procname">存储过程名称</param>
        /// <param name="sqltrans">事务</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static bool ExecuteProc(string Procname, SqlTransaction sqltrans, params SqlParameter[] parms)
        {
            try
            {
                int i = CssSQLHelper.CssExecuteNonQuery(sqltrans, CommandType.StoredProcedure, Procname, parms);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 数据检索方法

        #region SQL方法
        /// <summary>
        /// 按SQL语句检索数据，返回DataTable
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <returns></returns>
        public static DataTable ExecuteSQLToDataTable(string sqlstr)
        {
            try
            {
                return CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按SQL语句检索数据，返回DataTable
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <returns></returns>
        public static DataTable ExecuteSQLToDataTable(string sqlstr, string TableName)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null);
                dt.TableName = TableName;
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 按SQL语句检索数据，返回DataTable
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="sqlparm">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteSQLToDataTable(string sqlstr, params SqlParameter[] sqlparm)
        {
            try
            {
                return CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, sqlparm);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 按SQL语句检索数据，返回DataTable
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <param name="sqlparm">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteSQLToDataTable(string sqlstr, string TableName, params SqlParameter[] sqlparm)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, sqlparm);
                dt.TableName = TableName;
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按SQL语句检索数据，返回DataTable
        /// 带有连接和事务的操作，慎用！
        /// 注意事务的Commit和Rollback
        /// 注意连接的关闭（close）
        /// </summary>
        /// <param name="myConnection">数据连接</param>
        /// <param name="myTransaction">数据事务</param>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <param name="sqlparm">参数</param>
        /// <returns></returns>
        public static DataTable ExecuteSQLToDataTable(SqlConnection myConnection, SqlTransaction myTransaction, string sqlstr, string TableName, params SqlParameter[] sqlparm)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(myConnection, myTransaction, CommandType.Text, sqlstr, sqlparm);
                dt.TableName = TableName;
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按SQL语句检索数据，返回DataSet
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <returns></returns>
        public static DataSet ExecuteSQLToDataSet(string sqlstr)
        {
            try
            {
                DataSet ds = new DataSet();
                ds.Merge(CssSQLHelper.CssExecuteQueryToDataSet(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null));
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按SQL语句检索数据，返回DataSet
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <returns></returns>
        public static DataSet ExecuteSQLToDataSet(string sqlstr, string TableName)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null);
                dt.TableName = TableName;
                DataSet ds = new DataSet();
                ds.Merge(dt);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按SQL语句检索单个值
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <returns></returns>
        public static object ExcuteSQLScalar(string sqlstr)
        {
            return CssSQLHelper.CssExecuteScalar(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, null);
        }

        /// <summary>
        /// 按SQL语句检索单个值
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns></returns>
        public static object ExcuteSQLScalar(string sqlstr, params SqlParameter[] parms)
        {
            return CssSQLHelper.CssExecuteScalar(GlobalRunParms.PubdataConncetionString, CommandType.Text, sqlstr, parms);
        }
        #endregion

        #region Proc方法
        /// <summary>
        /// 按Proc名称检索数据，返回DataTable
        /// </summary>
        /// <param name="Procname">Proc名称</param>
        /// <param name="parms">参数，没有参数时为null</param> 
        /// <returns></returns>
        public static DataTable ExecuteProcToDataTable(string Procname, params SqlParameter[] parms)
        {
            try
            {
                return CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按Proc名称检索数据，返回DataTable
        /// </summary>
        /// <param name="Procname">Proc名称</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static DataTable ExecuteProcToDataTable(string Procname, string TableName, params SqlParameter[] parms)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
                dt.TableName = TableName;
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按Proc名称检索数据，返回DataSet
        /// </summary>
        /// <param name="Procname">Proc名称</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static DataSet ExecuteProcToDataSet(string Procname, params SqlParameter[] parms)
        {
            try
            {
                return CssSQLHelper.CssExecuteQueryToDataSet(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按Proc名称检索数据，返回DataSet
        /// </summary>
        /// <param name="Procname">Proc名称</param>
        /// <param name="TableName">DataTable的表名</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static DataSet ExecuteProcToDataSet(string Procname, string TableName, params SqlParameter[] parms)
        {
            try
            {
                DataTable dt = CssSQLHelper.CssExecuteQueryToDataTable(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
                dt.TableName = TableName;
                DataSet ds = new DataSet();
                ds.Merge(dt);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 按Proc名称检索单个值
        /// </summary>
        /// <param name="Procname">Proc名称</param>
        /// <param name="parms">参数，没有参数时为null</param>
        /// <returns></returns>
        public static object ExcuteProcScalar(string Procname, params SqlParameter[] parms)
        {
            return CssSQLHelper.CssExecuteScalar(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
        }
        /// <summary>
        /// 按SQL语句检索返回SqlDataReader进行查询
        /// </summary>
        /// <param name="SqlStr">检索用SQL语句</param>
        /// <returns>SqlDataReader，用与查询</returns>
        public static SqlDataReader GetSQLDataReader(string SqlStr)
        {
            return CssSQLHelper.CssExecuteReader(GlobalRunParms.PubdataConncetionString, CommandType.Text, SqlStr, null);
        }
        /// <summary>
        /// 按SQL语句检索返回SqlDataReader进行查询
        /// </summary>
        /// <param name="SqlStr">检索用SQL语句</param>
        /// <param name="parms">参数</param>
        /// <returns>SqlDataReader，用与查询</returns>
        public static SqlDataReader GetSQLDataReader(string SqlStr, params SqlParameter[] parms)
        {
            return CssSQLHelper.CssExecuteReader(GlobalRunParms.PubdataConncetionString, CommandType.Text, SqlStr, parms);
        }
        /// <summary>
        /// 按存储过程名称检索返回SqlDataReader进行查询
        /// </summary>
        /// <param name="Procname">存储过程名称</param>
        /// <param name="parms">参数</param>
        /// <returns>SqlDataReader，用与查询</returns>
        public static SqlDataReader GetProcDataReader(string Procname, params SqlParameter[] parms)
        {
            return CssSQLHelper.CssExecuteReader(GlobalRunParms.PubdataConncetionString, CommandType.StoredProcedure, Procname, parms);
        }

        #endregion

        #endregion

        /// <summary>
        /// 对DataSet进行保存
        /// </summary>
        /// <param name="selectCmd">保存的SQL语句</param>
        /// <param name="srcTable">保存的表名（DataSet中的DataTable的名称）</param>
        /// <param name="updateDataSet">需要保存的DataSet</param>
        /// <param name="pTr">数据库通讯事务</param>
        /// <returns>返回True或False</returns>
        public static bool UpdateByDataSet(string selectCmd, string srcTable, DataSet updateDataSet)
        {
            return CssSQLHelper.CssUpdateByDataSet(GlobalRunParms.PubdataConncetionString, selectCmd, srcTable, updateDataSet);
        }

        /// <summary>
        /// 对DataSet进行保存
        /// </summary>
        /// <param name="selectCmd">保存的SQL语句</param>
        /// <param name="srcTable">保存的表名（DataSet中的DataTable的名称）</param>
        /// <param name="updateDataSet">需要保存的DataSet</param>
        /// <param name="pTr">数据库通讯事务</param>
        /// <returns>返回True或False</returns>
        public static bool UpdateByDataSet(string selectCmd, string srcTable, DataSet updateDataSet, SqlConnection sqlconnect, SqlTransaction pTr)
        {
            return CssSQLHelper.CssUpdateByDataSet(sqlconnect, selectCmd, srcTable, updateDataSet, pTr);
        }

        public static bool UpdateByDataSet(string selectCmd, string srcTable, string UpdateCmd, SqlParameter[] srcSqlParameter, DataSet updateDataSet, SqlConnection sqlconnect, SqlTransaction pTr)
        {
            return CssSQLHelper.CssUpdateByDataSet(sqlconnect, selectCmd, srcTable, UpdateCmd, srcSqlParameter, updateDataSet, pTr);
        }

    }
}
